
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Text;


namespace CrmDocman.DataCore {
    /// <summary>
    /// Implementation of DataConnection
    /// </summary>
    public abstract class DataBridgeAbs : IDao  {

        private const string TIMESTAMP_COLUMN = "TIMESTAMP";        
        private const string PARAM_PREFIX = "@";
        private const string SEPARATE_CHARACTER = ",";
        private const string DOT = ".";
        private const string EXTENDED_TABLE_PREFIX = "ExTable";
        private const string EXTENDED_COLUMN_PREFIX = "Ex_";
        private const string SPACE = " ";
        // connection
        private ConnectionWrapper m_connectionDto = null;
        private int m_maxReturnRows = Int32.MaxValue;

        protected DataBridgeAbs() {
        }

        private static ConnectionWrapper s_environmentConnection;
        public static ConnectionWrapper EnvironmentConnection {
            get {
                return s_environmentConnection;
            }
            set {
                s_environmentConnection = value;
            }
        }

        ~DataBridgeAbs() {
            if (Connection != null && Connection.Connection.State == ConnectionState.Open) {
                Connection.CloseConnection();
            }
        }

        public void FillDataTable(DataTable table, string sql, params IDbDataParameter[] parameters) {
            FillDataTable(table, sql, CommandType.Text,string.Empty,string.Empty, string.Empty, parameters);
        }

        public void FillDataTable(DataTable table, string sql, CommandType type,
                                            string p_join_str, string p_where_str, string p_sort_str,
                                            params IDbDataParameter[] parameters) {
            IDbCommand command = null;
            try {
                // get command
                command = GetCommand(sql, type, parameters);
                //FOR CRM DocMan: add 3 params for @p_join_str nvarchar(4000), @p_where_str nvarchar(4000),
                // @p_sort_str nvarchar(4000) to integrate with ISD store procedure
                if (type == CommandType.StoredProcedure && sql.EndsWith("GetList")) {
                    if (p_join_str != string.Empty) {
                        IDbDataParameter p_join_strParam = GetInputParameter("@p_join_str", DbType.String, p_join_str);
                        command.Parameters.Add(p_join_strParam);
                    } else {
                        IDbDataParameter p_join_strParam = GetInputParameter("@p_join_str", DbType.String, DBNull.Value);
                        command.Parameters.Add(p_join_strParam);
                    }
                    if (p_where_str != string.Empty) {
                        IDbDataParameter p_where_strParam = GetInputParameter("p_where_str", DbType.String, p_where_str);
                        command.Parameters.Add(p_where_strParam);
                    } else {
                        IDbDataParameter p_where_strParam = GetInputParameter("@p_where_str", DbType.String, DBNull.Value);
                        command.Parameters.Add(p_where_strParam);
                    }
                    if (p_sort_str != string.Empty) {
                        IDbDataParameter p_sort_strParam = GetInputParameter("@p_sort_str", DbType.String, p_sort_str);
                        command.Parameters.Add(p_sort_strParam);
                    } else {
                        IDbDataParameter p_sort_strParam = GetInputParameter("@p_sort_str", DbType.String, DBNull.Value);
                        command.Parameters.Add(p_sort_strParam);
                    }
                }
                
                // Fill table
                if (type == CommandType.StoredProcedure && sql.EndsWith("GetList")) {
                    AdapterWrapper.FillDataTable(table, command, true);
                } else {
                    AdapterWrapper.FillDataTable(table, command);
                }

                // accept table changes
                table.AcceptChanges();
            } finally {
                // close command
                CloseCommand(command);
            }
        }

        public void FillDataTable(DataTable table, string sql, CommandType type,
                                    string p_join_str, string p_where_str, string p_sort_str, 
                                    int count, params IDbDataParameter[] parameters) {
            IDbCommand command = null;
            try {
                // get command
                command = GetCommand(sql, type, parameters);
                //FOR CRM DocMan: add 3 params for @p_join_str nvarchar(4000), @p_where_str nvarchar(4000),
                // @p_sort_str nvarchar(4000) to integrate with ISD store procedure
                if (type == CommandType.StoredProcedure && sql.EndsWith("GetList")) {
                    if (p_join_str != string.Empty) {
                        IDbDataParameter p_join_strParam = GetInputParameter("@p_join_str", DbType.String, p_join_str);
                        command.Parameters.Add(p_join_strParam);
                    } else {
                        IDbDataParameter p_join_strParam = GetInputParameter("@p_join_str", DbType.String, DBNull.Value);
                        command.Parameters.Add(p_join_strParam);
                    }
                    if (p_where_str != string.Empty) {
                        IDbDataParameter p_where_strParam = GetInputParameter("p_where_str", DbType.String, p_where_str);
                        command.Parameters.Add(p_where_strParam);
                    } else {
                        IDbDataParameter p_where_strParam = GetInputParameter("@p_where_str", DbType.String, DBNull.Value);
                        command.Parameters.Add(p_where_strParam);
                    }
                    if (p_sort_str != string.Empty) {
                        IDbDataParameter p_sort_strParam = GetInputParameter("@p_sort_str", DbType.String, p_sort_str);
                        command.Parameters.Add(p_sort_strParam);
                    } else {
                        IDbDataParameter p_sort_strParam = GetInputParameter("@p_sort_str", DbType.String, DBNull.Value);
                        command.Parameters.Add(p_sort_strParam);
                    }
                }
                
                // Fill table
                AdapterWrapper.FillDataTable(table, command);

                count = table.Rows.Count;

                // accept table changes
                table.AcceptChanges();
            } finally {
                // close command
                CloseCommand(command);
            }
        }

        public int UpdateDataTable(DataTable table, IDbCommand insertCmd, IDbCommand updateCmd, IDbCommand deleteCmd) {
            int result = 0;
            try {

                // update table
                result = AdapterWrapper.UpdateDataTable(table, insertCmd, updateCmd, deleteCmd);

                // accept table changes
                table.AcceptChanges();
            }  finally {
                // close command
                CloseCommand(insertCmd);
                CloseCommand(updateCmd);
                CloseCommand(deleteCmd);
            }

            return result;
        }

        public int UpdateDataTable(DataTable table) {
            return UpdateDataTable(table, null);
        }

        public int UpdateDataTable(DataTable table, string[] excludedColumns) {
            int result = 0;
            IDbCommand insertCmd = null;
            IDbCommand updateCmd = null;
            IDbCommand deleteCmd = null;
            try {
                string insertSql;
                IDbDataParameter[] insertParams = BuildInsertQuery(table,
                    excludedColumns, out insertSql);
                insertCmd = GetCommand(insertSql, insertParams);

                string updateSql;
                IDbDataParameter[] updateParams = BuildUpdateQuery(table,
                    excludedColumns, out updateSql);
                updateCmd = GetCommand(updateSql, updateParams);

                string deleteSql;
                IDbDataParameter[] deleteParams = BuildDeleteQuery(table, out deleteSql);
                deleteCmd = GetCommand(deleteSql, deleteParams);

                // update table
                result = UpdateDataTable(table, insertCmd, updateCmd, deleteCmd);

                // accept table changes
                table.AcceptChanges();
            } finally {
                // close command
                CloseCommand(insertCmd);
                CloseCommand(updateCmd);
                CloseCommand(deleteCmd);
            }

            return result;
        }
   
        protected int Insert(DataTable table, string[] excludedColumns) {
            string insertSql;
            IDbDataParameter[] insertParams = BuildInsertQuery(table,
                excludedColumns, out insertSql);
            IDbCommand command = GetCommand(insertSql, insertParams);

            return UpdateDataTable(table, command, null, null);
        }

        /// <summary>
        /// Insert data table
        /// </summary>
        /// <param name="table">table to insert</param>        
        protected int Insert(DataTable table) {
            return Insert(table, new String[] { });
        }

        public int Update(DataTable table, string[] excludedColumns) {
            string updateSql;
            IDbDataParameter[] updateParams = BuildUpdateQuery(table,
                excludedColumns, out updateSql);
            IDbCommand command = GetCommand(updateSql, updateParams);

            return UpdateDataTable(table, null, command, null);
        }

        /// <summary>
        /// Delete data table
        /// </summary>
        /// <param name="table">table to delete</param>        
        protected int Delete(DataTable table) {
            return Delete(table, new String[] { });
        }

        /// <summary>
        /// Delete data table
        /// </summary>
        /// <param name="table">table to delete</param>        
        protected int Delete(DataTable table, string[] excludedColumns) {
            string deleteSql;
            IDbDataParameter[] deleteParams = BuildDeleteQuery(table, out deleteSql);
            IDbCommand command = GetCommand(deleteSql, deleteParams);

            return UpdateDataTable(table, null, null, command);
        }

        /// <summary>
        /// Update data table
        /// </summary>
        /// <param name="table">table to update</param>        
        public int Update(DataTable table) {
            return Update(table, new String[] { });
        }

        /// <summary>
        /// Execute reader.User has to control connection manually
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public IDataReader ExecuteReader(string sql, params IDbDataParameter[] parameters) {
            return ExecuteReader(sql, CommandType.Text, parameters);
        }

        /// <summary>
        /// Execute reader.User has to control connection manually
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public IDataReader ExecuteReader(string sql, CommandType type, params IDbDataParameter[] parameters) {

            IDataReader reader = null;
            IDbCommand command = null;
            try {
                // create command
                command = GetCommand(sql, type, parameters);
                // execute reader
                reader = command.ExecuteReader();
            } finally {
                // close command
                CloseCommand(command);
            }
            return reader;
        }
        /// <summary>
        /// Execute non query
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        public int ExecuteNonQuery(string sql, params IDbDataParameter[] parameters) {
            return ExecuteNonQuery(sql, CommandType.Text, parameters);
        }
        /// <summary>
        /// Execute non query
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        public int ExecuteNonQuery(string sql, CommandType type, params IDbDataParameter[] parameters) {
            int result = 0;
            IDbCommand command = null;
            try {
                // create command
                command = GetCommand(sql, type, parameters);

                // execute command
                result = command.ExecuteNonQuery();
            } finally {
                // close command
                CloseCommand(command);
            }
            return result;
        }

        /// <summary>
        /// Execute scalar
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public object ExecuteScalar(string sql, params IDbDataParameter[] parameters) {
            return ExecuteScalar(sql, CommandType.Text, parameters);
        }

        /// <summary>
        /// Execute scalar
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public object ExecuteScalar(string sql, CommandType type, params IDbDataParameter[] parameters) {
            object result = null;
            IDbCommand command = null;
            try {
                // get command
                command = GetCommand(sql, type, parameters);
                // execute command
                result = command.ExecuteScalar();
            } finally {
                // close command
                CloseCommand(command);
            }
            return result;
        }


        #region Command
        /// <summary>
        /// Get command from sql statement and parameters
        /// </summary>
        /// <param name="sql">sql statement</param>
        /// <param name="parameters">parameters</param>
        /// <returns>Command</returns>
        protected IDbCommand GetCommand(string sql, params IDbDataParameter[] parameters) {
            return GetCommand(sql, CommandType.Text, parameters);
        }
        /// <summary>
        /// Get command
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        protected IDbCommand GetCommand(string sql, CommandType type, params IDbDataParameter[] parameters) {
            // create command from connection
            IDbCommand command = Connection.Connection.CreateCommand();

            // set connection transaction to command transaction
            command.Transaction = Connection.Transaction;
            // set command text
            command.CommandText = sql;

            command.CommandType = type;
            // add paramters
            AddParametersToCommand(command, parameters);

            return command;
        }

        // add the dbParams collection to command.Parameters.
        private void AddParametersToCommand(IDbCommand command, params IDbDataParameter[] parameters) {
            if (parameters == null) {
                return;
            }
            int count = parameters.Length;
            for (int i = 0; i < count; i++) {
                command.Parameters.Add(parameters[i]);
            }
        }

        #endregion Command

        #region IParameter
        /// <summary>
        /// Create input paramter
        /// </summary>
        /// <param name="name">name of param</param>
        /// <param name="type">type of param</param>
        /// <param name="value">value of param</param>
        /// <returns></returns>
        public IDbDataParameter GetInputParameter(string name, DbType type, object value) {
            return GetParameter(name, type, value, String.Empty, ParameterDirection.Input);
        }

        /// <summary>
        /// Create input paramter
        /// </summary>
        /// <param name="name">name of param</param>
        /// <param name="type">type of param</param>
        /// <param name="columnName">name of column</param>
        /// <returns></returns>
        public IDbDataParameter GetInputColumnParameter(string name, DbType type, String columnName) {
            return GetParameter(name, type, null, columnName, ParameterDirection.Input);
        }

        /// <summary>
        /// Create output paramter
        /// </summary>
        /// <param name="name">name of param</param>
        /// <param name="type">type of param</param>
        /// <param name="value">value of param</param>
        /// <returns></returns>
        protected IDbDataParameter GetOutputParameter(string name, DbType type, object value) {
            return GetParameter(name, type, value, String.Empty, ParameterDirection.Output);
        }

        /// <summary>
        /// Create output paramter
        /// </summary>
        /// <param name="name">name of param</param>
        /// <param name="type">type of param</param>
        /// <param name="columnName">name of column</param>
        /// <returns></returns>
        public IDbDataParameter GetOutputColumnParameter(string name, DbType type, String columnName) {
            return GetParameter(name, type, null, columnName, ParameterDirection.Output);
        }

        /// <summary>
        /// Create input/output paramter
        /// </summary>
        /// <param name="name">name of param</param>
        /// <param name="type">type of param</param>
        /// <param name="value">value of param</param>
        /// <returns></returns>
        protected IDbDataParameter GetInOutParameter(string name, DbType type, object value) {
            return GetParameter(name, type, value, String.Empty, ParameterDirection.InputOutput);
        }

        /// <summary>
        /// Create input/output paramter
        /// </summary>
        /// <param name="name">name of param</param>
        /// <param name="type">type of param</param>
        /// <param name="columnName">name of column</param>
        /// <returns></returns>
        public IDbDataParameter GetInOutColumnParameter(string name, DbType type, String columnName) {
            return GetParameter(name, type, null, columnName, ParameterDirection.InputOutput);
        }

        /// <summary>
        /// Get parameter 
        /// </summary>
        /// <param name="name">name of param</param>
        /// <param name="type">type of param</param>
        /// <param name="value">value of param</param>
        /// <param name="columnName">name of column</param>
        /// <param name="directition">direction</param>
        public IDbDataParameter GetParameter(string name, DbType type, object value, String columnName, ParameterDirection directition) {
            IDbDataParameter param = AdapterWrapper.CreateNewParameter();

            param.ParameterName = name;
            param.DbType = type;
            param.Direction = directition;

            if (columnName == null || String.Empty.Equals(columnName)) {
                param.Value = value;
            } else {
                param.SourceColumn = columnName;
            }

            return param;
        }

        #endregion IParameter

        #region Release command
        private DbType GetDBType(System.Type theType) {
            IDbDataParameter param = AdapterWrapper.CreateNewParameter();
            System.ComponentModel.TypeConverter tc;
            tc = System.ComponentModel.TypeDescriptor.GetConverter(param.DbType);
            if (tc.CanConvertFrom(theType)) {
                param.DbType = (DbType) tc.ConvertFrom(theType.Name);
            } else {
                // try to forcefully convert
                try {
                    param.DbType = (DbType) tc.ConvertFrom(theType.Name);
                } catch  {
                    param.DbType = DbType.Binary;
                    // ignore the exception
                    
                }
            }
            return param.DbType;
        }
        /// <summary>
        /// Close command
        /// </summary>
        /// <param name="command"></param>
        private void CloseCommand(IDbCommand command) {
            if (command != null) {
                command.Dispose();
            }
        }
        /// <summary>
        /// Close reader
        /// </summary>
        /// <param name="reader"></param>
        protected void CloseReader(IDataReader reader) {
            if (reader != null) {
                reader.Close();
            }
        }

        #endregion Release command

        #region Build query
        /// <summary>
        /// Build insert query for a given table.
        /// All columns that are not excluded will have correspond params.
        /// No requirement for excluded columns need to be in the table,
        /// Supports: + Tabble
        /// timestamp concurrency for a timestamp column named TimeStamp
        /// Support
        /// </summary>
        /// <param name="table">table contains metadata information</param>
        /// <param name="excludedColumns">List name of columns not included in the query</param>
        /// <param name="sql">sql string created</param>
        /// <returns>List of paramters needed for the query, use column mapping params</returns>
        protected IDbDataParameter[] BuildInsertQuery(DataTable table, string[] excludedColumns, out string sql) {

            List<IDbDataParameter> paramList = new List<IDbDataParameter>();           

            StringBuilder columnStr = new StringBuilder();
            StringBuilder paramStr = new StringBuilder();
            StringBuilder postfixStr = new StringBuilder();
            
            // copy to list
            IList exColumnList = new ArrayList();
            if (excludedColumns != null) {
                foreach (string column in excludedColumns) {
                    exColumnList.Add(column);
                }
            }

            string identityColumn = String.Empty;
            if (table.PrimaryKey.Length == 1 && table.PrimaryKey[0].AutoIncrement) {
                identityColumn = table.PrimaryKey[0].ColumnName;
            } 

            string columnName;
            string paramName;
            IDbDataParameter param;
            foreach (DataColumn column in table.Columns) {
                columnName = column.ColumnName;
                paramName = PARAM_PREFIX + columnName;
                
                if(columnName.StartsWith(EXTENDED_TABLE_PREFIX)
                    || columnName.StartsWith(EXTENDED_COLUMN_PREFIX)
                    || exColumnList.Contains(columnName)) {
                    continue;
                }

                DbType columnDbType = GetDBType(column.DataType);
                if (columnName.ToUpper() == TIMESTAMP_COLUMN) {
                    param = GetOutputColumnParameter(paramName, columnDbType, columnName);
                    param.Size = 8;
                    paramList.Add(param);

                    postfixStr.Append(" SET ").Append(paramName).Append(" = @@DBTS ");

                } else if (identityColumn == columnName) {
                    param = GetOutputColumnParameter(paramName, columnDbType, columnName);
                    paramList.Add(param);
                    postfixStr.Append(" SET ").Append(paramName).Append(" = @@IDENTITY ");
                } else {                    
                    columnStr.Append(columnName).Append(SEPARATE_CHARACTER);
                    paramStr.Append(paramName).Append(SEPARATE_CHARACTER);
                    paramList.Add(GetInputColumnParameter(paramName, columnDbType, columnName));
                    
                }                
            }
            if (columnStr.Length > 0) {
                string columnListStr = columnStr.ToString(0, columnStr.Length - 1);
                string paramListStr = paramStr.ToString(0, paramStr.Length - 1);

                sql = " INSERT INTO " + table.TableName + " (" + columnListStr + ")"
                        + " VALUES (" + paramListStr + "); " + postfixStr;
            } else {
                sql = "";
            }
            return paramList.ToArray();       
        }
        /// <summary>
        /// Build update query for a given table.
        /// Requirement: + Table column and excluded columns must create a column to be updated.
        ///              + no requirement for key columns
        /// Support concurency query for table with concurency timestamp column.
        /// </summary>
        /// <param name="table">table contains metadata information</param>
        /// <param name="excludedColumns">List name of columns not included in the query</param>
        /// <param name="sql">sql string created</param>
        /// <returns></returns>
        protected IDbDataParameter[] BuildUpdateQuery(DataTable table, string[] excludedColumns, out string sql) {
            StringBuilder setClause = new StringBuilder();
            StringBuilder whereClause = new StringBuilder();
            List<IDbDataParameter> paramList = new List<IDbDataParameter>();

            string columnName;
            string paramName;
            string postfixClause = "";
            // build none param columns list 
            List<string> noneParams = new List<string>();
            List<string> keyLists = new List<string>();
            //add key columns to noneparams list
            foreach (DataColumn keyColumn in table.PrimaryKey) {
                noneParams.Add(keyColumn.ColumnName);
                keyLists.Add(keyColumn.ColumnName);
            }
            if (excludedColumns != null) {
                //add excluded columns to noneparams list
                foreach (string s in excludedColumns) {
                    noneParams.Add(s);
                }
            }

            //create param and build the set clause 
            foreach (DataColumn column in table.Columns) {
               
                columnName = column.ColumnName;
                paramName = PARAM_PREFIX + columnName;

                if (columnName.StartsWith(EXTENDED_TABLE_PREFIX)
                    || columnName.StartsWith(EXTENDED_COLUMN_PREFIX)) {
                    continue;
                }

                bool isExcluded = false;
                //test if column is in excluded list
                foreach (string s in noneParams) {
                    if (s == columnName) {
                        isExcluded = true;
                        break;//exit the loop
                    }
                }
                if (isExcluded) {
                    //column is in excluded list, --> ignore it
                    continue;
                }
                DbType columnDbType = GetDBType(column.DataType);
               
                if (columnName.ToUpper() == TIMESTAMP_COLUMN) {
                    if (whereClause.Length > 0) {
                        whereClause.Append(" AND ");
                    }
                    whereClause.Append(columnName).Append(" = ").Append(paramName).Append(" ");
                    paramList.Add(GetInOutColumnParameter(paramName, columnDbType, columnName));
                    postfixClause = " SET " + paramName + " = @@DBTS";
                } else {
                    setClause.Append(" ").Append(columnName).Append(" = ").Append(paramName).Append(SEPARATE_CHARACTER);
                    paramList.Add(GetInputColumnParameter(paramName, columnDbType, columnName));
                }
            }
            //create where clause and create params
            foreach (DataColumn column in table.PrimaryKey) {
                columnName = column.ColumnName;
                paramName = PARAM_PREFIX + columnName;
                
                DbType columnDbType = GetDBType(column.DataType);
                if (whereClause.Length > 0) {
                    whereClause.Append(" AND ");
                }
                whereClause.Append(columnName).Append(" = ").Append(paramName).Append(" ");
                paramList.Add(GetInputColumnParameter(paramName, columnDbType, columnName));
            }
            if (setClause.Length <= 0) {
                //no update in DB, ignore all param  
                sql = "";
                paramList.Clear();
            } else {
                string setClauseStr = setClause.ToString(0,setClause.Length -1);
                string whereClauseStr = whereClause.ToString(0,whereClause.Length-1);
                sql = " UPDATE " + table.TableName + " SET " + setClauseStr;
                if (whereClauseStr.Trim().Length != 0) {
                    sql += " WHERE " + whereClauseStr;
                }
                sql += postfixClause;
            }
            return paramList.ToArray();
        }
        /// <summary>
        /// Build delete query for a given table.
        /// Requirement: + Table must have key column(s) 
        /// Support concurency delete with concurency timetamp column
        /// </summary>
        /// <param name="table">table contains metadata information</param>
        /// <param name="sql">sql string created</param>
        /// <returns>List of paramters needed for the query, use column mapping params</returns>
        protected IDbDataParameter[] BuildDeleteQuery(DataTable table, out string sql) {
            //Build order
            //+ Build where clause + paramlist 
            //+ append timestamp 
            //+ build last result
            StringBuilder whereClause = new StringBuilder();
            List<IDbDataParameter> paramList = new List<IDbDataParameter>();

            string columnName;
            string paramName;

            //s1
            foreach (DataColumn column in table.PrimaryKey) {
                columnName = column.ColumnName;
                paramName = PARAM_PREFIX + columnName;

                DbType columnDbType = GetDBType(column.DataType);
                if (whereClause.Length > 0) {
                    whereClause.Append(" AND ");
                }
                whereClause.Append(columnName).Append(" = ").Append(paramName);
                paramList.Add(GetInputColumnParameter(paramName, columnDbType, columnName));
            }
            if (whereClause.Length <= 0) {
                //no primary keys -->no input keys -->ignore this
                sql = "";
                paramList.Clear();
            } else {
                //s2
                foreach (DataColumn column in table.Columns) {
                    columnName = column.ColumnName;
                    if (columnName.ToUpper() == TIMESTAMP_COLUMN) {
                        
                        paramName = PARAM_PREFIX + columnName;

                        DbType columnDbType = GetDBType(column.DataType);
                        if (whereClause.Length > 0) {
                            whereClause.Append(" AND ");
                        }
                        whereClause.Append(columnName).Append(" = ").Append(paramName);
                        paramList.Add(GetInputColumnParameter(paramName, columnDbType, columnName));
                    }
                }
                //s3 
                sql = " DELETE FROM " + table.TableName + " WHERE " + whereClause;
            }

            return paramList.ToArray();

        }

        /// <summary>
        /// Fill data from databaset to the table.
        /// </summary>
        /// <param name="table"></param>
        public void FillDataTable(DataTable table) {
            FillDataTable(table, BuildSelectQuery(table));
        }

        /// <summary>
        /// Build the select query for all column of a table
        /// </summary>
        /// <param name="table"></param>
        /// <returns>The select query</returns>
        public String BuildSelectQuery(DataTable table) {
            StringBuilder str = new StringBuilder(" SELECT ");
            foreach (DataColumn column in table.Columns) {
                if (column.ColumnName.StartsWith(EXTENDED_COLUMN_PREFIX) || column.ColumnName.StartsWith(EXTENDED_TABLE_PREFIX)) {
                    continue;
                }
                str.Append(column.ColumnName).Append(",");
            }
            str.Remove(str.Length - 1, 1);
            str.Append(" FROM ").Append(table.TableName);
            return str.ToString();
        }

        public string BuildSelectQueryWithoutFromWhere(DataTable table) {
            return BuildSelectQueryWithoutFromWhere(table, null, null);
        }

        public string BuildSelectQueryWithoutFromWhere(DataTable table, string[] excludedColumns) {
            return BuildSelectQueryWithoutFromWhere(table, excludedColumns, null);
        }

        public string BuildSelectQueryWithoutFromWhere(DataTable table, string[] excludedColumns, string[] excludedTables) {
            // copy to list
            IList exColumnList = BuildList(excludedColumns);
            IList exTableList = BuildList(excludedTables);            

            StringBuilder result = new StringBuilder(" SELECT ");
            string tableName = table.TableName + DOT;
            foreach (DataColumn column in table.Columns) {
                string columnName = column.ColumnName;
                if (exColumnList.Contains(columnName) || columnName.StartsWith(EXTENDED_COLUMN_PREFIX)) {
                    continue;
                }
                if (columnName.StartsWith(EXTENDED_TABLE_PREFIX)) {
                    int index = columnName.IndexOf(EXTENDED_COLUMN_PREFIX);
                    if (index < 0) {
                        continue;
                    }
                    string exTableName = columnName.Substring(0, index).Replace(EXTENDED_TABLE_PREFIX, String.Empty);
                    string exColumnName = columnName.Substring(index).Replace(EXTENDED_COLUMN_PREFIX, String.Empty);

                    if (!exTableList.Contains(exTableName)) {
                        result.Append(exTableName).Append(DOT).Append(exColumnName);
                        result.Append(SPACE);
                        result.Append(columnName);
                        result.Append(SEPARATE_CHARACTER);
                    }
                }else {
                    result.Append(tableName).Append(columnName).Append(SEPARATE_CHARACTER);
                }
            }

            if (result.Length > 0) {
                result.Remove(result.Length - 1, 1);
            }

            return result.ToString();
        }

        protected string BuildGroupByQuery(DataTable table, string[] excludedColumns, string[] excludedTables) {
            IList exColumnList = BuildList(excludedColumns);
            IList exTableList = BuildList(excludedTables);

            StringBuilder result = new StringBuilder();
            string tableName = table.TableName + DOT;
            foreach (DataColumn column in table.Columns) {
                string columnName = column.ColumnName;
                if (exColumnList.Contains(columnName) || columnName.StartsWith(EXTENDED_COLUMN_PREFIX)) {
                    continue;
                }

                if (columnName.StartsWith(EXTENDED_TABLE_PREFIX)) {
                    int index = columnName.IndexOf(EXTENDED_COLUMN_PREFIX);
                    if (index < 0) {
                        continue;
                    }
                    string exTableName = columnName.Substring(0, index).Replace(EXTENDED_TABLE_PREFIX, String.Empty);
                    string exColumnName = columnName.Substring(index).Replace(EXTENDED_COLUMN_PREFIX, String.Empty);

                    if (!exTableList.Contains(exTableName)) {
                        result.Append(exTableName).Append(DOT).Append(exColumnName);
                        result.Append(SEPARATE_CHARACTER);
                    }
                } else {
                    result.Append(tableName).Append(columnName).Append(SEPARATE_CHARACTER);
                }
            }

            if (result.Length > 0) {
                result.Remove(result.Length - 1, 1);
            }
            return result.ToString();
        }

        private IList BuildList(string[] list) {
            IList result = new ArrayList();
            if (list != null) {
                foreach (string item in list) {
                    result.Add(item);
                }
            }
            return result;
        }
        #endregion Build query


        #region SProperties
        #endregion SProperties

        #region IProperties
        /// <summary>
        /// Connection property 
        /// </summary>
        public ConnectionWrapper Connection {
            //set {
            //    m_connectionDto = value;
            //}
            //get {
            //    return DataBridgeAbs.EnvironmentConnection;
            //}
            get {
                return this.m_connectionDto;
            }
            set {
                this.m_connectionDto = value;
            }
        }

        /// <summary>
        /// Number of max return rows;
        /// </summary>
        public int MaxReturnRows {
            set {
                m_maxReturnRows = value;
            }
            get {
                return m_maxReturnRows;
            }
        }
        #endregion IProperties

    }
}

